package com.example.demo.mapper;

import java.math.BigDecimal;
import java.util.List;

import org.apache.ibatis.annotations.Select;

import com.example.demo.dto.ActiveUserDto;
import com.example.demo.dto.TurnoverDto;

public interface DataStatisticsMapper {
	//统计营业额
	@Select("SELECT SUM(oprice) FROM orders")
	public BigDecimal getAllTurnover();
	//统计近一个月用户量
	@Select("SELECT COUNT(*) FROM orders where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= otime")
	public int getAllUserNum();
	//统计分院个数
	@Select("SELECT COUNT(*) FROM cinema")
	public int getAllCinema();
	//统计好评率
	@Select("SELECT COUNT(*) FROM appraise")  //所有评价个数
	public int getAllAppraise();
	@Select("SELECT COUNT(*) FROM appraise where agrade>=4")  //好评个数
	public int getAllGoodAppraise();

	//统计观看动作类电影人数
	@Select("SELECT COUNT(*) FROM orders,movie WHERE orders.mid=movie.id AND mtype like '%动作%'")
	public int getAmovie();
	//查询观看悬疑类电影人数
	@Select("SELECT COUNT(*) FROM orders,movie WHERE orders.mid=movie.id AND mtype like '%悬疑%'")
	public int getDmovie();
	//查询观看剧情类电影人数
	@Select("SELECT COUNT(*) FROM orders,movie WHERE orders.mid=movie.id AND mtype like '%剧情%'")
	public int getFmovie();
	//查询观看喜剧类电影人数
	@Select("SELECT COUNT(*) FROM orders,movie WHERE orders.mid=movie.id AND mtype like '%喜剧%'")
	public int getCmovie();
	//查询观看爱情类电影人数
	@Select("SELECT COUNT(*) FROM orders,movie WHERE orders.mid=movie.id AND mtype like '%爱情%'")
	public int getRmovie();
	//查询观看科幻类电影人数
	@Select("SELECT COUNT(*) FROM orders,movie WHERE orders.mid=movie.id AND mtype like '%奇幻%'")
	public int getSmovie();
	//按年查询营业额
	@Select("select sum(oprice) as value,date_format(otime,'%m') as name from orders where year(otime)=#{odata} group by name")
	public List<TurnoverDto> getTurnoverByYear(String odata);
	//查询活跃用户人数
	@Select("select COUNT(uid) as value,date_format(otime,'%m') as name from orders where year(otime)=#{odata} group by name")
	public List<ActiveUserDto> getActiveUser(String odata);
}
